using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Reflection;
using System.IO;
using System.Globalization;
using Microsoft.Office.Project.EPMSync.Config.Library;

namespace Microsoft.Office.Project.EPMSync.Service
{
    /// <summary>
    /// Class for verifying the database objects are in place, and the correct version.
    /// If the object don't exist or the version is incorrect it will attempt to
    /// create or update the objects as appropriate.
    /// </summary>
    public class DatabaseSetup
    {

        private const string CREATE_esQUEUE_ATTRIBUTE_EVENTS_FILE = "CREATE esQUEUE_ATTRIBUTE_EVENTS.sql";
        private const string CREATE_esQUEUE_ATTRIBUTE_EVENTS_ARCHIVE_FILE = "CREATE esQUEUE_ATTRIBUTE_EVENTS_ARCHIVE.sql";
        private const string CREATE_esATTRIBUTE_EVENT_FILE = "CREATE esATTRIBUTE_EVENT.sql";
        private const string CREATE_esGET_QUEUE_ATTRIBUTE_EVENTS_FILE = "CREATE esGET_QUEUE_ATTRIBUTE_EVENTS.sql";
        private const string CREATE_esGET_QUEUE_ATTRIBUTE_EVENTS_ARCHIVE_FILE = "CREATE esGET_QUEUE_ATTRIBUTE_EVENTS_ARCHIVE.sql";

        private const string CREATE_START = "-- Create Start (DO NOT DELETE)";
        private const string CREATE_END = "-- Create End (DO NOT DELETE)";

        private const string CREATE_EP_START = "-- Create Extended Property Start (DO NOT DELETE)";
        private const string CREATE_EP_END = "-- Create Extended Property End (DO NOT DELETE)";

        private const string READ_EP_START = "-- Read Extended Property Start (DO NOT DELETE)";
        private const string READ_EP_END = "-- Read Extended Property End (DO NOT DELETE)";

        private SqlConnection _connectionString;
        private Tracing _tracing;

        /// <summary>
        /// Constructor for the DatabaseSetup object
        /// </summary>
        /// <param name="_connectionString">Connection to the PPS database where the EPMSync objects will be installed.</param>
        /// <param name="_tracing">Refernce to the _tracing instance to output to the trace log.</param>
        public DatabaseSetup(SqlConnection connectionString, Tracing tracing)
        {
            _connectionString = connectionString;
            _tracing = tracing;
        }

        /// <summary>
        /// Static methods for kicking off the the database object verification.
        /// </summary>
        /// <returns>If all objects are successfully verified, or updated to the correct version, returns true.</returns>
        public bool VerifyDBObjects()
        {
            if ((_tracing != null) && (_connectionString != null))
            {
                if (!VerifyDBObject(CREATE_esQUEUE_ATTRIBUTE_EVENTS_FILE, "esQUEUE_ATTRIBUTE_EVENTS", "U"))
                    return false;

                if (!VerifyDBObject(CREATE_esQUEUE_ATTRIBUTE_EVENTS_ARCHIVE_FILE, "esQUEUE_ATTRIBUTE_EVENTS_ARCHIVE", "U"))
                    return false;

                if (!VerifyDBObject(CREATE_esATTRIBUTE_EVENT_FILE, "esATTRIBUTE_EVENT", "TR"))
                    return false;

                if (!VerifyDBObject(CREATE_esGET_QUEUE_ATTRIBUTE_EVENTS_FILE, "esGET_QUEUE_ATTRIBUTE_EVENTS", "P"))
                    return false;

                if (!VerifyDBObject(CREATE_esGET_QUEUE_ATTRIBUTE_EVENTS_ARCHIVE_FILE, "esGET_QUEUE_ATTRIBUTE_EVENTS_ARCHIVE", "P"))
                    return false;

                return true;
            }

            return false;
        }

        /// <summary>
        /// Implements the workflow of verifying each single database object.
        /// </summary>
        /// <param name="sqlFileName">The file name of the Sql script associated with the object.</param>
        /// <param name="objectName">The object name that will be verified.</param>
        /// <param name="objectType">The Sql object type for the object specified in objectName.</param>
        /// <returns>If the object is successfully verified, or updated to the correct version, returns true.</returns>
        private bool VerifyDBObject(string sqlFileName, string objectName, string objectType)
        {
            // Check the object exists
            try
            {
                string cmdExistsString = String.Format(CultureInfo.InvariantCulture, "SELECT OBJECT_ID('{0}', '{1}')", objectName, objectType);
                SqlCommand cmdExists = new SqlCommand(cmdExistsString, _connectionString);
                if (cmdExists.ExecuteScalar() != DBNull.Value)
                {
                    // Object Exists
                    string createEPString = ReadSQLFileString(sqlFileName, CREATE_EP_START, CREATE_EP_END);
                    if (createEPString.Length == 0)
                        return false;

                    string fileVersion = GetVersionString(createEPString);
                    string objectVersion = GetDBObjectVersion(sqlFileName).ToString();
                    if (objectVersion.Length == 0)
                        return false;

                    if (objectVersion != fileVersion)
                    {
                        // Update with the latest scripts
                        if (DropSQLObject(objectName, objectType))
                            if (CreateSQLObject(sqlFileName))
                                if (CreateSQLExProp(sqlFileName))
                                    return true;
                    }
                    else
                    {
                        // Object exists, and the file version is correct
                        return true;
                    }
                }
                else
                {
                    // Object deoesn't exist, so create it
                    if (CreateSQLObject(sqlFileName))
                        if (CreateSQLExProp(sqlFileName))
                            return true;
                }
            }
            catch (SqlException sqlEx)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "A SQL exception occured verifing database objects.  Exception: {0}", sqlEx.Message));
                return false;
            }
            catch (Exception ex)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "An exception occured verifing database objects.  Exception: {0}", ex.Message));
                return false;
            }

            // If any of the creations fail we'll fall to here
            return false;
        }

        /// <summary>
        /// Determines the version of the object already in the database.
        /// </summary>
        /// <param name="sqlFileName">The file name of the Sql script associated with the object.</param>
        /// <returns>A string representing the database object version in the form of yyyy.mm.dd.vv</returns>
        private string GetDBObjectVersion(string sqlFileName)
        {
            try
            {
                SqlCommand cmdCreate = new SqlCommand(ReadSQLFileString(sqlFileName, READ_EP_START, READ_EP_END), _connectionString);
                return cmdCreate.ExecuteScalar().ToString();
            }
            catch (SqlException sqlEx)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "A SQL exception occured reading DB object version.  Exception: {0}", sqlEx.Message));
                return string.Empty;
            }
            catch (Exception ex)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "An exception occured reading DB object version.  Exception: {0}", ex.Message));
                return string.Empty;
            }
        }

        /// <summary>
        /// Creates an extended property on the database object that specifies the version of the object.
        /// </summary>
        /// <param name="sqlFileName">The file name of the Sql script associated with the object.</param>
        /// <returns>If the property is successfully created on the object, returns true.</returns>
        private bool CreateSQLExProp(string sqlFileName)
        {
            try
            {
                SqlCommand cmdCreate = new SqlCommand(ReadSQLFileString(sqlFileName, CREATE_EP_START, CREATE_EP_END), _connectionString);
                cmdCreate.ExecuteNonQuery();
                return true;
            }
            catch (SqlException sqlEx)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "A SQL exception occured creating extended properties.  Exception: {0}", sqlEx.Message));
                return false;
            }
            catch (Exception ex)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "An exception occured creating extended properties.  Exception: {0}", ex.Message));
                return false;
            }
        }

        /// <summary>
        /// Creates a database object that is specified by the Sql script file.
        /// </summary>
        /// <param name="sqlFileName">The file name of the Sql script associated with the object.</param>
        /// <returns>If the object is successfully created, returns true.</returns>
        private bool CreateSQLObject(string sqlFileName)
        {
            try
            {
                SqlCommand cmdCreate = new SqlCommand(ReadSQLFileString(sqlFileName, CREATE_START, CREATE_END), _connectionString);
                cmdCreate.ExecuteNonQuery();
                return true;
            }
            catch (SqlException sqlEx)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "A SQL exception occured creating a SQL Object.  Exception: {0}", sqlEx.Message));
                return false;
            }
            catch (Exception ex)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "An exception occured creating a SQL Object.  Exception: {0}", ex.Message));
                return false;
            }
        }

        /// <summary>
        /// Deletes a database object that is specified by the Sql script file.
        /// </summary>
        /// <param name="objectName">The object name that will be deleted.</param>
        /// <param name="objectType">The Sql object type for the object specified in objectName.</param>
        /// <returns>If the object is successfully deleted, returns true.</returns>
        private bool DropSQLObject(string ObjectName, string ObjectType)
        {
            string objectTypeName = string.Empty;
            if (ObjectType.Contains("U"))
                objectTypeName = "TABLE";
            else if (ObjectType.Contains("TR"))
                objectTypeName = "TRIGGER";

            string dropString = string.Format(CultureInfo.InvariantCulture, "DROP {0} [dbo].[{1}]", objectTypeName, ObjectName);
            try
            {
                SqlCommand cmdDrop = new SqlCommand(dropString, _connectionString);
                cmdDrop.ExecuteNonQuery();
                return true;
            }
            catch (SqlException sqlEx)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "A SQL exception occured dropping a SQL Object.  Exception: {0}", sqlEx.Message));
                return false;
            }
            catch (Exception ex)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "An exception occured dropping a SQL Object.  Exception: {0}", ex.Message));
                return false;
            }
        }

        /// <summary>
        /// Parses the version to remove all the extra charaters.
        /// </summary>
        /// <param name="parseString">The version string to parse.</param>
        /// <returns>The version string in the format of yyyy.mm.dd.vv</returns>
        private static string GetVersionString(string parseString)
        {
            int valLoc = parseString.IndexOf("@value");
            int valStartQuote = parseString.IndexOf("'", valLoc);
            int valEndQuote = parseString.IndexOf("'", valStartQuote + 1);
            return parseString.Substring(valStartQuote + 1, valEndQuote - valStartQuote - 1);

        }

        /// <summary>
        /// Reads the specified Sql code snipet from the Sql script file.
        /// </summary>
        /// <param name="sqlFileName">The file name of the Sql script associated with the object.</param>
        /// <param name="startString">Delimits the start of the string</param>
        /// <param name="endString"Delimits the end of the string></param>
        /// <returns>The sql script statments to perfom the desired operation.</returns>
        private string ReadSQLFileString(string sqlFileName, string startString, string endString)
        {
            StringBuilder sb = new StringBuilder(4000);
            bool addToString = false;
            bool foundStart = false;
            bool foundEnd = false;

            try
            {
                Assembly epmAssembly = Assembly.GetExecutingAssembly();
                string fullPath = epmAssembly.Location;
                string directory = fullPath.Substring(0, fullPath.LastIndexOf(@"\") + 1);
                sqlFileName = directory + sqlFileName;

                // Verify the sync file exists.
                FileInfo fi = new FileInfo(sqlFileName);
                if (fi.Exists)
                {
                    using (StreamReader sr = new StreamReader(sqlFileName))
                    {
                        String line;
                        while ((line = sr.ReadLine()) != null)
                        {
                            if (line == startString)
                            {
                                addToString = true;
                                foundStart = true;
                            }
                            else if (line == endString)
                            {
                                addToString = false;
                                foundEnd = true;
                                break;
                            }
                            else if (addToString)
                            {
                                sb.AppendLine(line);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "An exception occured read SQL script file.  Exception: {0}", ex.Message));
                return null;
            }

            if (foundStart && foundEnd)
                return sb.ToString();
            else
                return null;
        }



    }
}
